package ru.reaperoq.schema

import kotlinx.serialization.Serializable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.`java-time`.CurrentDateTime
import org.jetbrains.exposed.sql.`java-time`.datetime
import org.jetbrains.exposed.sql.transactions.transaction
import ru.reaperoq.utils.dbQuery
import ru.reaperoq.utils.toEpochMillis
import ru.reaperoq.utils.toLocalDateTime

@Serializable
data class Computer(
    val id: Int,
    val serialNumber: String,
    val model: String,
    val manufacturer: String,
    val purchaseDate: Long,
    val locationId: Int,
    val status: ComputerStatus,
    val notes: String?,
    val createdAt: Long,
    val createdBy: Int,
    val verifiedBy: Int,
    val lastVerifiedAt: Long
)

object Computers : Table("computers") {
    val id: Column<Int> = integer("id").autoIncrement()
    val serialNumber: Column<String> = varchar("serial_number", 512).uniqueIndex()
    val model: Column<String> = varchar("model", 256)
    val manufacturer: Column<String> = varchar("manufacturer", 256)
    val purchaseDate = datetime("purchase_date")
    val status: Column<ComputerStatus> =
        enumerationByName("status", 20, ComputerStatus::class).default(ComputerStatus.IN_USE)
    val notes: Column<String?> = text("notes").nullable()
    val createdAt = datetime("created_at").defaultExpression(CurrentDateTime())
    val createdBy = reference("created_by", Users.id)

    val locationId = reference("location_id", Locations.id, onDelete = ReferenceOption.SET_NULL)
    val verifiedBy = reference("verified_by", Users.id, onDelete = ReferenceOption.NO_ACTION)
    val lastVerifiedAt = datetime("last_verified_at")
    override val primaryKey = PrimaryKey(id)
}

enum class ComputerStatus {
    IN_USE, IN_STORAGE, IN_REPAIR, DISPOSED
}

class ComputersService(private val database: Database) {
    init {
        transaction(database) {
            SchemaUtils.create(Computers)
        }
    }

    suspend fun create(
        serialNumber: String,
        model: String,
        manufacturer: String,
        purchaseDate: Long,
        locationId: Int,
        notes: String?,
        createdBy: Int
    ): Int = dbQuery {
        Computers.insert {
            it[Computers.serialNumber] = serialNumber
            it[Computers.model] = model
            it[Computers.manufacturer] = manufacturer
            it[Computers.purchaseDate] = purchaseDate.toLocalDateTime()
            it[Computers.locationId] = locationId
            it[Computers.notes] = notes
            it[Computers.createdBy] = createdBy
            it[verifiedBy] = createdBy
            it[lastVerifiedAt] = CurrentDateTime()
        }[Computers.id]
    }

    suspend fun update(
        id: Int,
        model: String,
        manufacturer: String,
        purchaseDate: Long,
        locationId: Int,
        notes: String?
    ): Int = dbQuery {
        println("TIME: $purchaseDate")
        Computers.update({ Computers.id eq id }) {
            it[Computers.model] = model
            it[Computers.manufacturer] = manufacturer
            it[Computers.purchaseDate] = purchaseDate.toLocalDateTime()
            it[Computers.locationId] = locationId
            it[Computers.notes] = notes
        }
    }

    suspend fun delete(id: Int) {
        dbQuery {
            Computers.deleteWhere { Computers.id eq id }
        }
    }

    suspend fun read(id: Int): Computer? {
        return dbQuery {
            Computers.select { Computers.id eq id }
                .map {
                    Computer(
                        it[Computers.id],
                        it[Computers.serialNumber],
                        it[Computers.model],
                        it[Computers.manufacturer],
                        it[Computers.purchaseDate].toEpochMillis(),
                        it[Computers.locationId],
                        it[Computers.status],
                        it[Computers.notes],
                        it[Computers.createdAt].toEpochMillis(),
                        it[Computers.createdBy],
                        it[Computers.verifiedBy],
                        it[Computers.lastVerifiedAt].toEpochMillis()
                    )
                }
                .singleOrNull()
        }
    }

    suspend fun readBySerial(serialNumber: String): Computer? {
        return dbQuery {
            Computers.select { Computers.serialNumber eq serialNumber }
                .map {
                    Computer(
                        it[Computers.id],
                        it[Computers.serialNumber],
                        it[Computers.model],
                        it[Computers.manufacturer],
                        it[Computers.purchaseDate].toEpochMillis(),
                        it[Computers.locationId],
                        it[Computers.status],
                        it[Computers.notes],
                        it[Computers.createdAt].toEpochMillis(),
                        it[Computers.createdBy],
                        it[Computers.verifiedBy],
                        it[Computers.lastVerifiedAt].toEpochMillis()
                    )
                }
                .singleOrNull()
        }
    }

    suspend fun readAll(): List<Computer> = dbQuery {
        Computers.selectAll()
            .map {
                Computer(
                    it[Computers.id],
                    it[Computers.serialNumber],
                    it[Computers.model],
                    it[Computers.manufacturer],
                    it[Computers.purchaseDate].toEpochMillis(),
                    it[Computers.locationId],
                    it[Computers.status],
                    it[Computers.notes],
                    it[Computers.createdAt].toEpochMillis(),
                    it[Computers.createdBy],
                    it[Computers.verifiedBy],
                    it[Computers.lastVerifiedAt].toEpochMillis()
                )
            }
    }

    suspend fun readByLocation(locationId: Int): List<Computer> = dbQuery {
        Computers.select { Computers.locationId eq locationId }
            .map {
                Computer(
                    it[Computers.id],
                    it[Computers.serialNumber],
                    it[Computers.model],
                    it[Computers.manufacturer],
                    it[Computers.purchaseDate].toEpochMillis(),
                    it[Computers.locationId],
                    it[Computers.status],
                    it[Computers.notes],
                    it[Computers.createdAt].toEpochMillis(),
                    it[Computers.createdBy],
                    it[Computers.verifiedBy],
                    it[Computers.lastVerifiedAt].toEpochMillis()
                )
            }
    }

    suspend fun verify(id: Int, verifiedBy: Int) {
        dbQuery {
            Computers.update({ Computers.id eq id }) {
                it[Computers.verifiedBy] = verifiedBy
                it[Computers.lastVerifiedAt] = CurrentDateTime()
            }
        }
    }
}
